package com.space.school.dao.impl;

import com.space.school.dao.ProductDao;
import com.space.school.domain.Category;
import com.space.school.domain.Product;
import com.space.school.domain.User;
import com.space.school.utils.SpaceJDBCUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.*;

public class ProductDaoImpl implements ProductDao {
    private JdbcTemplate jdbcTemplate = new JdbcTemplate(SpaceJDBCUtils.getDs());

    @Override
    public List<Product> search(int start, int pageSize, Map<String, String[]> map) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT product_info.*, category_subquery.category_name ")
                .append("FROM product_info ")
                .append("LEFT JOIN ( ")
                .append("    SELECT category_name, category_type ")
                .append("    FROM product_category ")
                .append(") AS category_subquery ON product_info.category_type = category_subquery.category_type ")
                .append("WHERE product_info.product_status LIKE ? "); // 初始WHERE条件

        ArrayList<Object> params = new ArrayList<>();
        params.add("%" + (map.getOrDefault("product_status", new String[]{""})[0]).trim() + "%"); // 默认添加product_status的LIKE条件，即使它不在map中

        // 取map键中的数组，并动态添加WHERE条件
        Set<String> keySet = map.keySet();
        for (String key : keySet) {
            if (!key.equals("currentPage") && !key.equals("product_status") && !map.get(key)[0].trim().isEmpty()) {
                stringBuffer.append("AND " + key + " LIKE ? ");
                params.add("%" + map.get(key)[0].trim() + "%");
            }
        }

        // 添加分页参数
        params.add(start);
        params.add(pageSize);

        // 完成SQL语句
        stringBuffer.append("ORDER BY product_id ")
                .append("LIMIT ?, ?");

        return jdbcTemplate.query(stringBuffer.toString(), new BeanPropertyRowMapper<>(Product.class), params.toArray());
    }

    @Override
    public int count(Map<String, String[]> map) {
        StringBuilder sqlBuilder = new StringBuilder();
        List<Object> params = new ArrayList<>();

        sqlBuilder.append("SELECT COUNT(*) FROM product_info a LEFT JOIN product_category b ON a.category_type = b.category_type WHERE 1=1");

        Set<String> keySet = map.keySet();
        for (String key : keySet) {
            if (!"currentPage".equals(key) && map.get(key) != null && map.get(key).length > 0 && !map.get(key)[0].isEmpty()) {
                sqlBuilder.append(" AND ").append(key).append(" LIKE ?");
                params.add("%" + map.get(key)[0] + "%");
            }
        }

        String sql = sqlBuilder.toString();

        try {
            return jdbcTemplate.queryForObject(sql, Integer.class, params.toArray());
        } catch (DataAccessException e) {
            // 处理数据库访问异常
            throw new RuntimeException("Error counting products", e);
        }
    }

    @Override
    public int delete(int id) {
        // 登录查询
        String sql = "delete from product_info where product_id=?";
        int result = jdbcTemplate.update(sql, id);
        return result;
    }

    @Override
    public void update(Map<String, String[]> map) {
        StringBuffer stringBuffer=new StringBuffer();
        stringBuffer.append("update product_info set ");
        //        取值的集合
        ArrayList<Object> params=new ArrayList<>();
        int id=0;
        Set<String> keyset=map.keySet();
        for (String key:keyset) {
            if(!map.get(key)[0].equals("")){
                if(key.equals("product_id")){
                    id=Integer.parseInt(map.get(key)[0]);
                }else {
                    stringBuffer.append(" " + key + " = ? ,");
                    if(key.equals("product_price")||key.equals("product_stock")||key.equals("product_status")||key.equals("category_type")){
                        params.add(Integer.parseInt(map.get(key)[0]));
                    }else {
                        params.add(" "+map.get(key)[0]+" ");
                    }
                }
            }
        }
        stringBuffer.deleteCharAt(stringBuffer.length()-1);
        stringBuffer.append(" where product_id=?");
        params.add(id);
        jdbcTemplate.update(stringBuffer.toString(),params.toArray());
    }

    @Override
    public Product findById(int id) {
        String sql = "select * from product_info where product_id=?";
        List<Product> product = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Product>(Product.class), id);
        if (product.size() > 0) {
            return product.get(0);
        }
        return null;
    }

    @Override
    public void updateStatus(int id, String status) {
        String sql = "UPDATE product_info SET product_status = ?,update_Time=? WHERE product_id = ? ";
        jdbcTemplate.update(sql, status, new Date(), id);
    }

    @Override
    public void save(Product product) {
        // 新增语句
        String sql = "insert into product_info (product_name,product_price,product_stock,product_description,product_icon,product_status,category_type) values(?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql, product.getProduct_name(), product.getProduct_price(),product.getProduct_stock(),product.getProduct_description(),product.getProduct_icon(),product.getProduct_status(),product.getCategory_type());
    }

    @Override
    public Product isInsert(String product_name) {
        String sql = "select * from product_info where product_name=?";
//        sql行的映射器  将sql 查询后的结果映射到集合中
        List<Product> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Product>(Product.class), product_name);
        if (list.size() > 0) {
            return list.get(0);
        } else {
            return null;
        }
    }
    @Override
    public List<Category> findAll() {
        String sql="select * from product_category  ";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Category>(Category.class));
    }
}
